"""
Case Type   : Timescaledb功能测试
Case Name   : 时序表的时间列类型为bigint，函数show_chunks正常使用
Create At   : 2025/05/13
Owner       : lihongji
Description :
              1.创建表及索引
              2.标准表转换为时序表
              3.添加时间列分区
              4.调用show_chunks函数
              5.清理环境
Expect      :
              1.创建表、索引成功
              2.转换为时序表成功
              3.时间列分区添加成功
              4.调用成功
              5.清理环境成功
History     :
"""

import unittest
import os

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class Timescaledb(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.constant = Constant()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbPGmode')
        self.db_user_node = Node(node='PrimaryDbPGmode')
        status = self.primary_sh.get_db_cluster_status()
        self.assertTrue("Normal" in status or "Degraded" in status)
        self.t_name_1 = 'timescaledb_chunkutils_case0003_1'
        self.t_name_2 = 'timescaledb_chunkutils_case0003_2'
        self.t_name_3 = 'timescaledb_chunkutils_case0003_3'
        select_extension_cmd = f"SELECT * FROM pg_extension;"
        select_extension_result = self.primary_sh.execut_db_sql(
            select_extension_cmd)
        if "timescaledb" not in select_extension_result:
            create_extension_cmd = f"create extension timescaledb;"
            create_extension_result = (
                self.primary_sh.execut_db_sql(create_extension_cmd))
            self.assertIn(self.constant.extension_create_success,
                          create_extension_result, '创建timescaledb扩展失败')

    def test_add_dimension(self):
        text = '----step1: 创建表及索引 expect: 创建表、索引成功----'
        self.log.info(text)
        create_table = f"""DROP TABLE IF EXISTS {self.t_name_1};
        DROP TABLE IF EXISTS {self.t_name_2};
        DROP TABLE IF EXISTS {self.t_name_3};
        CREATE TABLE PUBLIC.{self.t_name_1}(
        time bigint, temp float8, device_id text);
        CREATE TABLE PUBLIC.{self.t_name_2}(
        time bigint, temp float8, device_id text);
        CREATE TABLE PUBLIC.{self.t_name_3}(
        time bigint, temp float8, device_id text);
        CREATE INDEX ON {self.t_name_1}(time DESC);"""
        create_result = self.primary_sh.execut_db_sql(create_table)
        self.log.info(create_table)
        self.log.info(create_result)
        self.assertEqual(3, create_result.count(
            self.constant.TABLE_CREATE_SUCCESS), '执行失败' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG,
                      create_result, '执行失败' + text)

        text = '----step2: 标准表转换为时序表 expect: 转换为时序表成功----'
        self.log.info(text)
        create_hypertable = ("SELECT create_hypertable("
                             f"'public.{self.t_name_1}', 'time', "
                             "chunk_time_interval => 1, "
                             "create_default_indexes=>FALSE);"
                             "SELECT create_hypertable("
                             f"'public.{self.t_name_2}', 'time', "
                             "chunk_time_interval => 1, "
                             "create_default_indexes=>FALSE);"
                             "SELECT create_hypertable("
                             f"'public.{self.t_name_3}', 'time', "
                             "chunk_time_interval => 1, "
                             "create_default_indexes=>FALSE);")
        create_hypertable_result = self.primary_sh.execut_db_sql(
            create_hypertable)
        self.log.info(create_hypertable)
        self.log.info(create_hypertable_result)
        self.assertIn(self.t_name_1, create_hypertable_result, '执行失败' + text)
        self.assertIn(self.t_name_2, create_hypertable_result, '执行失败' + text)
        self.assertIn(self.t_name_3, create_hypertable_result, '执行失败' + text)

        text = '----step3:添加时间列分区 expect:时间列分区添加成功----'
        self.log.info(text)
        add_dimension = (
            f"SELECT add_dimension('public.{self.t_name_1}', 'device_id', 2);"
            f"SELECT add_dimension('public.{self.t_name_2}', 'device_id', 2);"
            f"SELECT add_dimension('public.{self.t_name_3}', 'device_id', 2);")
        add_dimension_result = self.primary_sh.execut_db_sql(add_dimension)
        self.log.info(add_dimension)
        self.log.info(add_dimension_result)
        self.assertIn(self.t_name_1, add_dimension_result, '执行失败' + text)
        self.assertIn(self.t_name_2, add_dimension_result, '执行失败' + text)
        self.assertIn(self.t_name_3, add_dimension_result, '执行失败' + text)

        text = '----step4:调用show_chunks函数 expect:调用成功----'
        self.log.info(text)
        show_chunks = "SELECT show_chunks()"
        show_chunks_result = self.primary_sh.execut_db_sql(show_chunks)
        self.log.info(show_chunks)
        self.log.info(show_chunks_result)
        self.assertIn('(0 rows)', show_chunks_result, '执行失败' + text)

    def tearDown(self):
        text = '-----step5:清理环境 expect:成功-----'
        self.log.info(text)
        drop_table = (f"drop table PUBLIC.{self.t_name_1};"
                      f"drop table PUBLIC.{self.t_name_2};"
                      f"drop table PUBLIC.{self.t_name_3}")
        drop_table_result = self.primary_sh.execut_db_sql(drop_table)
        self.assertEqual(3, drop_table_result.count(
            self.constant.DROP_TABLE_SUCCESS), '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
